package com.ksd1.notes.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import java.io.Serializable;
import java.util.LinkedList;
import java.util.List;


/**
 * 通用数据库
 * 含有两张表：todo note
 */
public class NoteDB {
    protected static final String TAG = NoteDB.class.getSimpleName();

    protected static final int DB_VERSION = 1;//数据库版本
    protected static final String DB_NAME = "notes.db";//数据库名

    protected static final String DB_PRIMARY_KEY = "_id";//主键

    // todo
    protected static final String DB_TABLE_NAME = "todo";//表名
    protected static final String DB_TABLE_COLUMN_CONTENT = "content";//内容
    protected static final String DB_TABLE_COLUMN_STATUS = "status";//状态、完成1 or未完成2
    //    protected static final String DB_DEFAULT_ORDERBY = DB_TABLE_COLUMN_DATE + " DESC";
    private final String DB_CREATE_TODO = "create table " + DB_TABLE_NAME + " (" +
            "_id integer primary key autoincrement," +
            DB_TABLE_COLUMN_CONTENT + " text not null," +
            DB_TABLE_COLUMN_STATUS + " integer not null" +
            ")";

    // note
    protected static final String N_DB_TABLE_NAME = "note";
    protected static final String N_DB_TABLE_COLUMN_TITLE = "title";//标题
    protected static final String N_DB_TABLE_COLUMN_CONTENT = "content";//内容
    protected static final String N_DB_TABLE_COLUMN_DATE = "date";//内容
    protected static final String N_DB_TABLE_COLUMN_TYPE = "type";//所属文件夹
    protected static final String DB_DEFAULT_ORDER = N_DB_TABLE_COLUMN_DATE + " DESC";//默认降序排序
    private final String DB_CREATE_NOTE = "create table " + N_DB_TABLE_NAME + " (" +
            "_id integer primary key autoincrement," +
            N_DB_TABLE_COLUMN_TITLE + " text not null," +
            N_DB_TABLE_COLUMN_CONTENT + " text not null," +
            N_DB_TABLE_COLUMN_DATE + " integer, " +
            N_DB_TABLE_COLUMN_TYPE + " integer not null" +
            ")";

    // 文件夹
    protected static final String Dir_DB_TABLE_NAME = "dir";//保存所有文价夹
    protected static final String Dir_DB_TABLE_COLUMN_NAME = "name";//文价夹名字
    private final String DB_CREATE_TYPE = "create table " + Dir_DB_TABLE_NAME + " (" +
            "_id integer primary key autoincrement," +
            Dir_DB_TABLE_COLUMN_NAME + " text not null" +
            ")";


    protected DatabaseHelper mDBHelper;
    protected SQLiteDatabase mDB;

    protected static final NoteDB mInstance = new NoteDB();//单例

    //代办事项
    public static class Todo {
        public long key = -1;
        public String content;
        public int status;

        public Todo(String content, int status) {
            this.content = content;
            this.status = status;
        }

        public Todo() {
        }
    }

    //日记
    public static class Note implements Serializable {
        public long key = -1;
        public String title;
        public String content;
        public long date;
        public long type;//所属文件夹 1代表未分类

        public Note(String title, String content, long date) {
            this.title = title;
            this.content = content;
            this.date = date;
        }

        public Note() {
        }
    }

    public static class Type {
        public long key;
        public String name;
    }

    //数据库帮助类
    protected class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DB_CREATE_TODO);//建造todo表
            db.execSQL(DB_CREATE_NOTE);
            db.execSQL(DB_CREATE_TYPE);

            //初始化dir表
            String isql = "insert into " + Dir_DB_TABLE_NAME + " values(1, \"" + "未分类" + "\")";
            db.execSQL(isql);
            isql = "insert into " + Dir_DB_TABLE_NAME + " values(2, \"" + "学习" + "\")";
            db.execSQL(isql);
            isql = "insert into " + Dir_DB_TABLE_NAME + " values(3, \"" + "生活" + "\")";
            db.execSQL(isql);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_NAME);//更新时删表重建
            db.execSQL("DROP TABLE IF EXISTS " + N_DB_TABLE_NAME);
            db.execSQL("DROP TABLE IF EXISTS " + Dir_DB_TABLE_NAME);
            onCreate(db);
        }
    }


    /***************方法开始****************/

    /**
     * 私有构造函数，单例
     */
    private NoteDB() {
    }

    public static NoteDB getInstance() {
        return mInstance;
    }

    /**
     * 初始化mDB
     *
     * @param context 获取数据库对象所需的context
     * @return true 初始化成功
     * false 初始化失败
     */
    public boolean open(Context context) {
        try {
            mDBHelper = new DatabaseHelper(context, DB_NAME, null, DB_VERSION);
            mDB = mDBHelper.getWritableDatabase();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 关闭数据库
     */
    public void close() {
        mDB.close();
        mDBHelper.close();
    }


    /**
     * @return 数据库todo中的所有数据中的
     */
    public List<Todo> query() {
        Cursor cursor = mDB.query(DB_TABLE_NAME, null, null, null, null, null, null, null);
        return extract(0, cursor);
    }

    /**
     * @param note 所有的Todo事项
     * @return 插入的结果
     */
    public boolean insert(List<Todo> note) {
        if (!deleteAll()) return false;//删除失败 直接返回

        int size = note.size();
        for (int i = 0; i < size; i++) {
            ContentValues values = new ContentValues();
            values.put(DB_TABLE_COLUMN_CONTENT, note.get(i).content);
            values.put(DB_TABLE_COLUMN_STATUS, note.get(i).status);
            long k = mDB.insert(DB_TABLE_NAME, null, values);
            if (k == -1) return false;
        }
        return true;
    }

    /**
     * 删除todo表中的所有数据
     *
     * @return boolean
     */
    private boolean deleteAll() {
//        int count = mDB.delete(DB_TABLE_NAME, null, null);
        mDB.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_NAME);
        mDB.execSQL(DB_CREATE_TODO);//重新建造该表
        return true;
    }

    /**
     * @param offset 偏移量（偏移多少条数据）
     * @param cursor 数据
     * @return todo列表
     */
    protected List<Todo> extract(int offset, Cursor cursor) {
        List<Todo> notes = new LinkedList<Todo>();
        if (cursor == null || cursor.getCount() <= offset) {
            return notes;
        }

        cursor.moveToFirst();
        cursor.moveToPosition(offset);//跳过的数据数量

        do {
            Todo note = new Todo();
            note.content = cursor.getString(cursor.getColumnIndex(DB_TABLE_COLUMN_CONTENT));
            note.status = cursor.getInt(cursor.getColumnIndex(DB_TABLE_COLUMN_STATUS));
            notes.add(note);
        } while (cursor.moveToNext());

        cursor.close();

        return notes;
    }


    /****************************日记操作****************************************/

    /**
     * 查询note表中的所有数据
     */
    public List<Note> nquery() {
        Cursor cursor = mDB.query(N_DB_TABLE_NAME, null, null, null, null, null, DB_DEFAULT_ORDER, null);
        return nextract(0, cursor);
    }

    /**
     * 获取指定类型的List
     *
     * @param type 类型
     * @return
     */
    public List<Note> nquery(long type) {
        String condition = N_DB_TABLE_COLUMN_TYPE + "=" + "\'" + type + "\'";
        Cursor cursor = mDB.query(N_DB_TABLE_NAME, null, condition, null, null, null, DB_DEFAULT_ORDER, null);
        return nextract(0, cursor);
    }

    protected List<Note> nextract(int offset, Cursor cursor) {
        List<Note> notes = new LinkedList<Note>();
        if (cursor == null || cursor.getCount() <= offset) {
            return notes;
        }

        cursor.moveToFirst();
        cursor.moveToPosition(offset);//跳过的数据数量

        do {
            Note note = new Note();
            note.key = cursor.getLong(cursor.getColumnIndex(DB_PRIMARY_KEY));
            note.title = cursor.getString(cursor.getColumnIndex(N_DB_TABLE_COLUMN_TITLE));
            note.content = cursor.getString(cursor.getColumnIndex(N_DB_TABLE_COLUMN_CONTENT));
            note.date = cursor.getLong(cursor.getColumnIndex(N_DB_TABLE_COLUMN_DATE));
            note.type = cursor.getLong(cursor.getColumnIndex(N_DB_TABLE_COLUMN_TYPE));//所属文件夹
            notes.add(note);
        } while (cursor.moveToNext());

        cursor.close();

        return notes;
    }

    /**
     * 通过主键获取note对象
     *
     * @param key 主键
     * @return Note
     */
    public Note nget(long key) {
        String condition = DB_PRIMARY_KEY + "=" + "\'" + key + "\'";
        Cursor cursor = mDB.query(N_DB_TABLE_NAME, null, condition, null, null, null, DB_DEFAULT_ORDER, null);
        return nextract(0, cursor).get(0);
    }

    /**
     * 更新指定位置的值
     *
     * @param key  位置
     * @param note 更新的值
     * @return boolean
     */
    public boolean nupdate(long key, Note note) {
        String condition = DB_PRIMARY_KEY + "=" + "\'" + key + "\'";
        ContentValues values = new ContentValues();
        values.put(N_DB_TABLE_COLUMN_TITLE, note.title);
        values.put(N_DB_TABLE_COLUMN_CONTENT, note.content);
        values.put(N_DB_TABLE_COLUMN_DATE, note.date);
        values.put(N_DB_TABLE_COLUMN_TYPE, note.type);

        mDB.update(N_DB_TABLE_NAME, values, condition, null);
        return true;
    }

    /**
     * 在note表插入一条数据
     *
     * @param note 插入的日记
     * @return long 插入后的主键
     */
    public long ninsert(Note note) {
        ContentValues values = new ContentValues();
        values.put(N_DB_TABLE_COLUMN_TITLE, note.title);
        values.put(N_DB_TABLE_COLUMN_CONTENT, note.content);
        values.put(N_DB_TABLE_COLUMN_DATE, note.date);
        values.put(N_DB_TABLE_COLUMN_TYPE, note.type);
        mDB.insert(N_DB_TABLE_NAME, null, values);
        return ngetPK();
    }

    private long ngetPK(){
        String sql = "select last_insert_rowid() from " + N_DB_TABLE_NAME ;
        Cursor cursor = mDB.rawQuery(sql, null);
        int a = -1;
        if(cursor.moveToFirst()){
            a = cursor.getInt(0);
        }
        return a;
    }

    public boolean nremove(long key) {
        String condition = DB_PRIMARY_KEY + "=" + "\'" + key + "\'";
        int i = mDB.delete(N_DB_TABLE_NAME, condition, null);
        return i > 0;
    }

    public boolean nremoveAll() {
        int i = mDB.delete(N_DB_TABLE_NAME, null, null);
        return i > 0;
    }

    public boolean nremoveAllType(long type){
        if(type == 0) return nremoveAll();
        String condition = N_DB_TABLE_COLUMN_TYPE + "=" + "\'" + type + "\'";
        int i = mDB.delete(N_DB_TABLE_NAME, condition, null);
        return i > 0;
    }

    /*****************************dir操作******************************/

    public List<Type> tquery() {
        Cursor cursor = mDB.query(Dir_DB_TABLE_NAME, null, null, null, null, null, null, null);
        return textract(0, cursor);
    }

    protected List<Type> textract(int offset, Cursor cursor) {
        List<Type> types = new LinkedList<Type>();
        if (cursor == null || cursor.getCount() <= offset) {
            return types;
        }

        cursor.moveToFirst();
        cursor.moveToPosition(offset);//跳过的数据数量

        do {
            Type type = new Type();
            type.key = cursor.getLong(cursor.getColumnIndex(DB_PRIMARY_KEY));
            type.name = cursor.getString(cursor.getColumnIndex(Dir_DB_TABLE_COLUMN_NAME));
            types.add(type);
        } while (cursor.moveToNext());

        cursor.close();

        return types;
    }


    /**
     * 获取类型的id
     *
     * @param name 类型名字
     * @return long
     */
    public long getType(String name) {
        long type = -1;
        List<Type> list = NoteDB.getInstance().tquery();
        for (int i = 0; i < list.size(); i++) {
            if (list.get(i).name.equals(name)) {
                type = list.get(i).key;
                break;
            }
        }

        return type;
    }


}
